package CGW.dao;

import CGW.anno.ForeignKey;
import CGW.anno.Table;
import CGW.bean.Dinner_table;
import CGW.bean.User;
import CGW.utils.DB;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDao<T> {
    private Connection conn;
    public Connection getConnect(){
        try {
            if(conn==null || conn.isClosed()){
                conn = DB.getDataSource().getConnection();
                conn.setAutoCommit(false);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public void commit(){
        if(conn!=null){
            try {
                conn.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public void rollback(){
        if(conn!=null){
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    private void extract_field_value(ResultSet rs, Field field, Object instance) throws SQLException, IllegalAccessException {
        field.setAccessible(true);  // 可修改字段值
        // 判断字段属性的类型
        switch (field.getType().getSimpleName()){
            case "Integer":
                field.set(instance, rs.getInt(field.getName()));
                break;
            case "float":
                field.set(instance, rs.getFloat(field.getName()));
                break;
            case "String":
                field.set(instance, rs.getString(field.getName()));
                break;
            case "Timestamp":
                field.set(instance, rs.getTimestamp(field.getName()));
                break;
            case "Double":
            case "double":
                field.set(instance, rs.getDouble(field.getName()));
                break;
            case "Date":
                field.set(instance, rs.getDate(field.getName()));
                break;
        }
    }
    //预定餐桌
    public int update_one(Dinner_table dinner_table){
        int count = 0;
        try {
            String sql = "update t_dinner_table set table_status =" + dinner_table.getTable_status() + " where table_id="+dinner_table.getTable_id();
            System.out.println(sql);
            Statement st = getConnect().createStatement();
            count = st.executeUpdate(sql);
            if (count != 0) {
                System.out.print("更新成功！！！ ");
            } else {
                System.out.print("update error!!! ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public int update_one_user_balance(User user){
        int count = 0;
        try {
            String sql = "update t_user set balance =" + user.getBalance() + " where user_id ="+user.getUser_id();
            System.out.println(sql);
            Statement st = getConnect().createStatement();
            count = st.executeUpdate(sql);
            if (count != 0) {
                System.out.print("更新成功！！！ ");
            } else {
                System.out.print("update error!!! ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public List<T> all(Class<?> cls){
        // 获取cls类上的Table注解的value值作为表名
        String table = cls.getAnnotation(Table.class).value();
        String sql = "select * from "+table;
        try {
            Statement t = getConnect().createStatement();
            ResultSet rs = t.executeQuery(sql);
            List<T> list = new ArrayList<>();
            while (rs.next()){
                Object obj = cls.newInstance();

                // 从rs中读取数据并设置obj对象的某一个属性
                for (Field f : cls.getDeclaredFields()) {
                    ForeignKey foreignKey = f.getAnnotation(ForeignKey.class);
                    if( foreignKey== null)
                        extract_field_value(rs,f, obj);
                    else{
                        f.setAccessible(true);
                        Field foreignkeyFiled = cls.getDeclaredField(foreignKey.value());
                        foreignkeyFiled.setAccessible(true);
                        f.set(obj, get(f.getType(), foreignKey.value(),foreignkeyFiled.get(obj)));
                    }
                }
                list.add((T)obj);
            }
            rs.close();
            t.close();
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int count_rows(Class<?> cls){
        // 统计表数据的总行数
        try{
            String sql = "select count(1) from "+cls.getAnnotation(Table.class).value();
            System.out.println(sql);
            ResultSet resultSet = getConnect().createStatement().executeQuery(sql);
            if(resultSet.next()){
                return resultSet.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    public List<T> all(Class<?> cls, int page, int pageSize){
        // 获取cls类上的Table注解的value值作为表名
        String table = cls.getAnnotation(Table.class).value();
        String sql = "select * from "+table;
        sql += " limit "+(page-1)*pageSize +","+pageSize;
        try {
            Statement t = getConnect().createStatement();
            ResultSet rs = t.executeQuery(sql);
            List<T> list = new ArrayList<>();
            while (rs.next()){
                Object obj = cls.newInstance();

                // 从rs中读取数据并设置obj对象的某一个属性
                for (Field f : cls.getDeclaredFields()) {
                    ForeignKey foreignKey = f.getAnnotation(ForeignKey.class);
                    if( foreignKey== null)
                        extract_field_value(rs,f, obj);
                    else{
                        f.setAccessible(true);
                        Field foreignkeyFiled = cls.getDeclaredField(foreignKey.value());
                        foreignkeyFiled.setAccessible(true);
                        f.set(obj, get(f.getType(), foreignKey.value(),foreignkeyFiled.get(obj)));
                    }
                }
                list.add((T)obj);
            }
            rs.close();
            t.close();
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 支持查询条件
    public List<T> query(Class<?> cls, int page, int pageSize, String queryWhere,Object ...queryParams){
        // 获取cls类上的Table注解的value值作为表名
        String table = cls.getAnnotation(Table.class).value();
        String sql = "select * from "+table;
        sql += " where "+ queryWhere + " limit "+(page-1)*pageSize +","+pageSize;
        try {
            PreparedStatement t = getConnect().prepareStatement(sql);
            for (int i = 0; i < queryParams.length; i++) {
                t.setObject(i+1, queryParams[i]);
            }
            ResultSet rs = t.executeQuery();
            List<T> list = new ArrayList<>();
            while (rs.next()){
                Object obj = cls.newInstance();

                // 从rs中读取数据并设置obj对象的某一个属性
                for (Field f : cls.getDeclaredFields()) {
                    ForeignKey foreignKey = f.getAnnotation(ForeignKey.class);
                    if( foreignKey== null)
                        extract_field_value(rs,f, obj);
                    else{
                        f.setAccessible(true);
                        Field foreignkeyFiled = cls.getDeclaredField(foreignKey.value());
                        foreignkeyFiled.setAccessible(true);
                        f.set(obj, get(f.getType(), foreignKey.value(),foreignkeyFiled.get(obj)));
                    }
                }
                list.add((T)obj);
            }
            rs.close();
            t.close();
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<T> query(Class<?> cls, String queryWhere, Object ...queryParams){
        // 获取cls类上的Table注解的value值作为表名
        String table = cls.getAnnotation(Table.class).value();
        String sql = "select * from "+table;
        sql += " where "+ queryWhere;
        try {
            PreparedStatement t = getConnect().prepareStatement(sql);
            for (int i = 0; i < queryParams.length; i++) {
                t.setObject(i+1, queryParams[i]);
            }
            ResultSet rs = t.executeQuery();
            List<T> list = new ArrayList<>();
            while (rs.next()){
                Object obj = cls.newInstance();

                // 从rs中读取数据并设置obj对象的某一个属性
                for (Field f : cls.getDeclaredFields()) {
                    ForeignKey foreignKey = f.getAnnotation(ForeignKey.class);
                    if( foreignKey== null)
                        extract_field_value(rs,f, obj);
                    else{
                        f.setAccessible(true);
                        Field foreignkeyFiled = cls.getDeclaredField(foreignKey.value());
                        foreignkeyFiled.setAccessible(true);
                        f.set(obj, get(f.getType(), foreignKey.value(),foreignkeyFiled.get(obj)));
                    }
                }
                list.add((T)obj);
            }
            rs.close();
            t.close();
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return null;
    }

    public T get(Class<?> cls, String pk, Object pkVal){
        String sql = "select * from "+cls.getAnnotation(Table.class).value();
        sql += " where "+pk+"=?";
        try {
            PreparedStatement t = getConnect().prepareStatement(sql);
            t.setObject(1, pkVal);

            ResultSet rs = t.executeQuery();
            if(rs.next()){
                Object obj = cls.newInstance();
                for (Field f : cls.getDeclaredFields()) {

                    extract_field_value(rs, f, obj);
                }
                rs.close();
                t.close();
                return (T)obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    public int update(T obj, String pk){
        Class<?> cls= obj.getClass();
        Field[] fields = cls.getDeclaredFields();
        String sql = "update "+cls.getAnnotation(Table.class).value() + " set ";
        for (Field f : fields) {
            if(! f.getName().equals(pk) && f.getAnnotation(ForeignKey.class)==null){
                sql += " "+f.getName()+"=?,";
            }
        }
        sql = sql.substring(0, sql.length()-1);
        sql += " where "+pk+"=?";
        System.out.println(sql);

        try {
            PreparedStatement t = getConnect().prepareStatement(sql);
            int index=1;
            Object pkVal = null;
            for (Field f : fields) {
                f.setAccessible(true);
                // 判断当前字段是否存在ForeignKey注解
                if(f.isAnnotationPresent(ForeignKey.class)) continue;
                if(! f.getName().equals(pk) ) {
                    t.setObject(index, f.get(obj));
                    index ++;
                }else{
                    pkVal = f.get(obj);
                }
            }

            t.setObject(index, pkVal);
            return t.executeUpdate();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    public int add(T obj, String ignoreField){
        Class<?> aClass = obj.getClass();
        Field[] fs = aClass.getDeclaredFields();
        String sql = "insert into "+aClass.getAnnotation(Table.class).value();
        String fs_str = "(";
        String val_str = "(";
        for (Field f : fs) {
            if(f.isAnnotationPresent(ForeignKey.class)) continue;
            if(! f.getName().equals(ignoreField)){
                fs_str += f.getName()+",";
                val_str += "?,";
            }

        }
        fs_str = fs_str.substring(0, fs_str.length()-1)+")";
        val_str = val_str.substring(0, val_str.length()-1) + ")";

        sql += fs_str +" values"+val_str;
        System.out.println(sql);
        try {
            PreparedStatement t = getConnect().prepareStatement(sql);
            int index=1;
            for (Field f : fs) {
                if(f.isAnnotationPresent(ForeignKey.class)) continue;
                if(!f.getName().equals(ignoreField)){
                    f.setAccessible(true);
                    t.setObject(index++, f.get(obj));
                }
            }
            return t.executeUpdate();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public int delete(Class<?> cls, String pk, Object pkVal){
        String sql = "delete from "+cls.getAnnotation(Table.class).value();
        sql += " where "+pk+"=?";
        try{
            PreparedStatement t = getConnect().prepareStatement(sql);
            t.setObject(1, pkVal);

            return t.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    public void close(){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void main(String[] args){
        BaseDao<User> dao = new BaseDao<>();
        int rows = dao.count_rows(User.class);
//        int page =2;
//        int pageSize = 2;
//        int maxPage = (int)Math.ceil(rows*1.0/pageSize);

        List<User> users = dao.all(User.class);
        for (User user : users) {
            System.out.println(user);

        }
    }

}
